package com.pi.wms.service;


import com.pi.framework.core.repository.IBaseRepository;
import com.pi.framework.core.service.impl.BaseCrudService;
import com.pi.framework.core.util.StringUtils;
import com.pi.pro.xcmg.am.common.XcmgAmConstants;
import com.pi.pro.xcmg.am.common.XcmgAmReportWorkSupport;
import com.pi.pro.xcmg.am.common.XcmgAmSupport;
import com.pi.pro.xcmg.am.common.vo.report.*;
import com.pi.wms.common.WmsConstants;
import com.pi.wms.common.WmsReportUtils;
import com.pi.wms.entity.TwtActTaskList;
import com.pi.wms.entity.TwtActTransList;
import com.pi.wms.repository.KanbanReportRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;


@Service
@Transactional
public class KanbanReportService extends BaseCrudService<TwtActTransList, Long>{


    @Autowired
    private KanbanReportRepository repository;

    @Autowired
    private DivisionService divisionService;

    @Autowired
    private CustDyncdataQueryService dyncdataQueryService;

    @Override
    protected IBaseRepository<TwtActTransList,Long> getRepository() {
        return this.repository;
    }


    @SuppressWarnings("unchecked")
    public InboundSummaryDailyReportDto summaryInboundDailyData(Map<String, Object> query) {
        InboundSummaryDailyReportDto reportData = null;

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeReceiveTransDailyData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<CommonTransLineDto> trans = XcmgAmReportWorkSupport.translateToTranslineData(result);
        //
        reportData = new InboundSummaryDailyReportDto();
        Set<Long> docids=new HashSet<>();
        if ((trans!=null) && (trans.size()>0)){
            for (CommonTransLineDto line:trans){
                if (line==null){
                    continue;
                }
                if (line.getDocumentid()!=null){
                    docids.add(line.getDocumentid());
                }
            }
        }
        reportData.setDocuemttotal(docids.size());
        reportData.setWorkfincount(trans.size());

        /**
         * 待上架数
         */
        nativeSql = XcmgAmReportWorkSupport.nativeStoragePutawayHoldSummaryDailyData(query);
        q = em.createNativeQuery(nativeSql);

        // 分页设置
//        q.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
//        q.setMaxResults(pageable.getPageSize());

        result = q.getResultList();

        // 记录实体转换
        List<CommonDataIdSummaryDto> divsummaries = XcmgAmReportWorkSupport.translateToIdSummaryData(result);
        if ((divsummaries!=null) && (divsummaries.size()>0)){
            reportData.setWorkholdcount(divsummaries.get(0).getTotal().intValue());
        }

        return reportData;
    }

    @SuppressWarnings("unchecked")
    public OutboundSummaryDailyReportDto summaryOutboundDailyData(Map<String, Object> query) {
        OutboundSummaryDailyReportDto reportData = null;

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeSoDocumentLineDailyData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<CommonSoDocumentLineDto> lines = XcmgAmReportWorkSupport.translateToSoDocumentLineData(result);
        //
        reportData = new OutboundSummaryDailyReportDto();
        int fincount=0;
        Set<Long> alldocids=new HashSet<>();
        Set<Long> findocids=new HashSet<>();
        Set<Long> lostdocids=new HashSet<>();
        Set<Long> shipdocids=new HashSet<>();
        BigDecimal qtyallocate=null;
        int ordertotal = 0;
        int shiptotal = 0;
        if ((lines!=null) && (lines.size()>0)){
            for (CommonSoDocumentLineDto d:lines){
                if (d==null){
                    continue;
                }
                ordertotal++;
                alldocids.add(d.getDocumentid());
                if (WmsConstants.SO_STATUS_CANCELL.endsWith(d.getLinestatus())){
                    continue;
                }
                if (WmsConstants.SO_STATUS_CLOSE.endsWith(d.getLinestatus())){
                    findocids.add(d.getDocumentid());
                }
                if (WmsConstants.SO_STATUS_SHIP.endsWith(d.getLinestatus())){
                    findocids.add(d.getDocumentid());
                }
                qtyallocate=BigDecimal.ZERO;
                if ((d.getQtyAllocated()!=null) && (d.getQtyAllocated().compareTo(BigDecimal.ZERO)>0)){
                    qtyallocate=d.getQtyAllocated();
                }
                if ((!StringUtils.isNullOrBlank(d.getWorkHandleCode()))
                        && (qtyallocate.compareTo(d.getQtyOrdered())<0)){
                    lostdocids.add(d.getDocumentid());
                }
                if ((d.getQtyShipped()!=null) && (d.getQtyShipped().compareTo(BigDecimal.ZERO)>0)){
                    shipdocids.add(d.getDocumentid());
                    shiptotal++;
                }
            }
        }
        reportData.setDocuemttotal(alldocids.size());
        reportData.setDocuemtfincount(findocids.size());
        reportData.setDocuemtlostcount(lostdocids.size());
        reportData.setOrdercount(ordertotal);
        reportData.setWorkfincount(shiptotal);

        return reportData;
    }

    @SuppressWarnings("unchecked")
    public List<OutboundWorklistDailyReportDto> outboundWorklistDailyData(Map<String, Object> query) {
        List<OutboundWorklistDailyReportDto> docs=new ArrayList<>();
        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeSoDocumentLineDailyData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<CommonSoDocumentLineDto> lines = XcmgAmReportWorkSupport.translateToSoDocumentLineData(result);
        if ((lines==null) || (lines.size()==0)){
            return docs;
        }
        /**
        Collections.sort(lines, new Comparator<CommonSoDocumentLineDto>() {
            @Override
            public int compare(CommonSoDocumentLineDto d1, CommonSoDocumentLineDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                int val=0;
                //DIVISION
                if ((d1.getDivisionid()!=null)&&(d2.getDivisionid()!=null)){
                    val = d1.getDivisionid().compareTo(d2.getDivisionid());
                    if (val!=0){
                        return val;
                    }
                }
                //TPRIOR
                val=StringUtils.ifNullOrBlank(d1.getDocumentprior(),WmsConstants.TASK_PRIOR_NORMAL).compareTo(StringUtils.ifNullOrBlank(d2.getDocumentprior(),WmsConstants.TASK_PRIOR_NORMAL));
                if (val!=0){
                    return val;
                }
                //DOCUMENT
                if ((d1.getDocumentnumber()!=null)&&(d2.getDocumentnumber()!=null)){
                    val = d1.getDocumentnumber().compareTo(d2.getDocumentnumber());
                    if (val!=0){
                        return val;
                    }
                }
                //DOCLINE
                val=StringUtils.ifNullOrBlank(d1.getLinenumber(),"0010").compareTo(StringUtils.ifNullOrBlank(d2.getLinenumber(),"0010"));
                if (val!=0){
                    return val;
                }
                //REQ-NUMBER/BULK_NUMBER
                if ((d1.getCreateddate()!=null)&&(d2.getCreateddate()!=null)){
                    val = d1.getCreateddate().compareTo(d2.getCreateddate());
                }
                return val;
            }
        });
         **/
        OutboundWorklistDailyReportDto doc=null;
        Map<String, OutboundWorklistDailyReportDto> docmap=new HashMap<>();
        for (CommonSoDocumentLineDto d:lines){
            if (d==null){
                continue;
            }
            doc=docmap.get(d.getDocumentnumber());
            if (doc!=null){
                continue;
            }
            if (WmsConstants.SO_STATUS_CLOSE.equalsIgnoreCase(d.getDocumentstatus())){
                continue;
            }
            if (WmsConstants.SO_STATUS_CANCELL.equalsIgnoreCase(d.getDocumentstatus())){
                continue;
            }
            if (WmsConstants.SO_STATUS_SHIP.equalsIgnoreCase(d.getDocumentstatus())){
                continue;
            }
            doc = new OutboundWorklistDailyReportDto();
            doc.setDivisionid(d.getDivisionid());
            doc.setDocumentnumber(d.getDocumentnumber());
            doc.setProductiondocnumber("");
            doc.setRefdocumentnumber(d.getRefnumber());
            doc.setProductionnumber(d.getRefnumber());
            doc.setWavenumber("");
            doc.setDocumentstatus(d.getDocumentstatus());
            doc.setDocumentstatusname(d.getDocumentstatus());
            doc.setDocumentprior(WmsConstants.TASK_PRIOR_NORMAL);
            if (!StringUtils.isNullOrBlank(d.getDocumentprior())){
                doc.setDocumentprior(d.getDocumentprior().trim());
            }
            doc.setSortationLocationCode(d.getSortationLocationCode());
            doc.setWorkHandleCode(d.getWorkHandleCode());
            doc.setNotes(d.getNotes());
            //作业状态
            //重要的缩写IMP
            //非常重要VIO,
            //紧急的IMMD、URG
            doc.setWorkstatus("");
            if (doc.getDocumentprior().compareTo(WmsConstants.TASK_PRIOR_NORMAL)<0){
                doc.setWorkstatus("IMMD");
            }
            doc.setCreateddate(d.getCreateddate());
            docs.add(doc);
            docmap.put(d.getDocumentnumber(), doc);
        }
        //
        Collections.sort(docs, new Comparator<OutboundWorklistDailyReportDto>() {
            @Override
            public int compare(OutboundWorklistDailyReportDto d1, OutboundWorklistDailyReportDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                int val=0;
                //TPRIOR
                val=StringUtils.ifNullOrBlank(d1.getDocumentprior(),WmsConstants.TASK_PRIOR_NORMAL).compareTo(StringUtils.ifNullOrBlank(d2.getDocumentprior(),WmsConstants.TASK_PRIOR_NORMAL));
                if (val!=0){
                    return val;
                }
                //CREATEDDATE
                if ((d1.getCreateddate()!=null)&&(d2.getCreateddate()!=null)){
                    val = d1.getCreateddate().compareTo(d2.getCreateddate());
                }
                return val;
            }
        });
        return docs;
    }

    @SuppressWarnings("unchecked")
    public List<OutboundAbnormallistDailyReportDto> outboundAbnormallistDailyData(Map<String, Object> query) {
        List<OutboundAbnormallistDailyReportDto> docs=new ArrayList<>();
        OutboundSummaryDailyReportDto reportData = null;

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeSoDocumentLineDailyData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<CommonSoDocumentLineDto> lines = XcmgAmReportWorkSupport.translateToSoDocumentLineData(result);
        if ((lines==null) || (lines.size()==0)){
            return docs;
        }
        //
        OutboundAbnormallistDailyReportDto doc=null;
        OutboundDocumentLineReportDto dline=null;
        List<OutboundDocumentLineReportDto> dlines=null;
        BigDecimal qtyallocated=BigDecimal.ZERO;
        Map<String, OutboundAbnormallistDailyReportDto> docmap=new HashMap<>();
        for (CommonSoDocumentLineDto d:lines){
            if (d==null){
                continue;
            }
            doc=docmap.get(d.getDocumentnumber());
            if (doc!=null){
                continue;
            }
            if (WmsConstants.SO_STATUS_CLOSE.equalsIgnoreCase(d.getDocumentstatus())){
                continue;
            }
            if (WmsConstants.SO_STATUS_CANCELL.equalsIgnoreCase(d.getDocumentstatus())){
                continue;
            }
            if (WmsConstants.SO_STATUS_SHIP.equalsIgnoreCase(d.getDocumentstatus())){
                continue;
            }
            //尚来指定拣选位
            if (StringUtils.isNullOrBlank(d.getWorkHandleCode())){
                continue;
            }
            qtyallocated=BigDecimal.ZERO;
            if ((d.getQtyAllocated()!=null) && (d.getQtyAllocated().compareTo(BigDecimal.ZERO))>0){
                qtyallocated=d.getQtyAllocated();
            }
            if (qtyallocated.compareTo(d.getQtyOrdered())>=0){
                continue;
            }
            if (doc==null){
                doc = new OutboundAbnormallistDailyReportDto();
                doc.setDivisionid(d.getDivisionid());
                doc.setDocumentnumber(d.getDocumentnumber());
                doc.setProductiondocnumber("");
                doc.setRefdocumentnumber(d.getRefnumber());
                doc.setProductionnumber(d.getRefnumber());
                doc.setWavenumber("");
                doc.setDocumentstatus(d.getDocumentstatus());
                doc.setDocumentstatusname(d.getDocumentstatus());
                doc.setDocumentprior(WmsConstants.TASK_PRIOR_NORMAL);
                if (!StringUtils.isNullOrBlank(d.getDocumentprior())){
                    doc.setDocumentprior(d.getDocumentprior().trim());
                }
                doc.setSortationLocationCode(d.getSortationLocationCode());
                doc.setWorkHandleCode(d.getWorkHandleCode());
                doc.setNotes(d.getNotes());
                //作业状态
                //重要的缩写IMP
                //非常重要VIO,
                //紧急的IMMD、URG
                doc.setWorkstatus("LOST");
                if (doc.getDocumentprior().compareTo(WmsConstants.TASK_PRIOR_NORMAL)<0){
                    doc.setWorkstatus("IMMD");
                }
                doc.setCreateddate(d.getCreateddate());
                docs.add(doc);
                docmap.put(d.getDocumentnumber(), doc);
            }
            dlines=doc.getLines();
            if (dlines==null){
                dlines=new ArrayList<>();
                doc.setLines(dlines);
            }
            dline = new OutboundDocumentLineReportDto();
            dline.setDivisionid(d.getDivisionid());
            dline.setLinenumber(d.getLinenumber());
            dline.setMaterialcode(d.getMaerialcode());
            dline.setMaterialname(d.getMaerialname());
            dline.setSpelmodel("");
            dline.setQtyordered(d.getQtyOrdered());
            dline.setQtyallocated(d.getQtyAllocated());
            dline.setQtyshipped(d.getQtyShipped());
            dline.setWorkstatus("");
            dlines.add(dline);
        }
        //
        Collections.sort(docs, new Comparator<OutboundAbnormallistDailyReportDto>() {
            @Override
            public int compare(OutboundAbnormallistDailyReportDto d1, OutboundAbnormallistDailyReportDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                int val=0;
                //TPRIOR
                val=StringUtils.ifNullOrBlank(d1.getDocumentprior(),WmsConstants.TASK_PRIOR_NORMAL).compareTo(StringUtils.ifNullOrBlank(d2.getDocumentprior(),WmsConstants.TASK_PRIOR_NORMAL));
                if (val!=0){
                    return val;
                }
                //CREATEDDATE
                if ((d1.getCreateddate()!=null)&&(d2.getCreateddate()!=null)){
                    val = d1.getCreateddate().compareTo(d2.getCreateddate());
                }
                return val;
            }
        });
        return docs;
    }

    @SuppressWarnings("unchecked")
    public StorageSummaryReportDto summaryStorageData(Map<String, Object> query) {
        StorageSummaryReportDto reportData = null;

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeStorageLaneSummaryDailyData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<StorageLaneSummaryAmountReportDto> lanes = XcmgAmReportWorkSupport.translateToStorageLaneAmountData(result);
        if ((lanes==null) || (lanes.size()==0)){
            reportData = new StorageSummaryReportDto();
            return reportData;
        }
        //
        reportData = new StorageSummaryReportDto();
        int total=0;
        int workcount=0;
        int fullcount=0;
        int halfcount=0;
        int epalfcount=0;
        int reservecount=0;
        for (StorageLaneSummaryAmountReportDto d:lanes){
            if (d==null){
                continue;
            }
            //fullcount=fullcount+d.getFullcontainercount();
            fullcount=fullcount+d.getWorkcount();
            halfcount=halfcount+d.getHalfcontainercount();
            epalfcount=epalfcount+d.getEmptycontainercount();
            reservecount=reservecount+d.getReservecount();
        }
        reportData.setFullcontainercount(fullcount);
        reportData.setHalfcontainercount(halfcount);
        reportData.setEmptycontainercount(epalfcount);
        reportData.setReservecount(reservecount);
        return reportData;
    }

    public StorageWarehouseSummaryReportDto summaryStorageWarehouseData(Map<String, Object> query) {
        StorageWarehouseSummaryReportDto reportData = null;

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeStorageWarehouseSummaryData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<StorageWarehouseSummaryReportDto> dlines = XcmgAmReportWorkSupport.translateToStorageWarehouseSummaryData(result);
        if ((dlines==null) || (dlines.size()==0)){
            reportData = new StorageWarehouseSummaryReportDto();
            return reportData;
        }
        Long divisionid=null;
        Object divisioncode=null;
        int freecount=0;
        for (StorageWarehouseSummaryReportDto d:dlines){
            if (d==null){
                continue;
            }
            freecount=d.getTotal()-d.getWorkalltotal();
            d.setFreetotal(freecount);

            reportData=d;
            divisionid=null;
            try{
                if ((query!=null) && (query.containsKey("divisioncode"))){
                    divisioncode=query.get("divisioncode");
                    divisionid=this.divisionService.retrieveOn(divisioncode.toString(),XcmgAmConstants.DIVISION_WH101_ID);
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            if (divisionid!=null){
                if (divisionid.equals(d.getDivisionid())){
                    break;
                }
            }
        }
        return reportData;
    }

    @SuppressWarnings("unchecked")
    public List<StorageLaneSummaryReportDto> summaryStorageLaneData(Map<String, Object> query) {
        List<StorageLaneSummaryReportDto> lanes=new ArrayList<>();

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeStorageLaneSummaryDailyData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        // 记录实体转换
        List<StorageLaneSummaryAmountReportDto>  datas = XcmgAmReportWorkSupport.translateToStorageLaneAmountData(result);
        if ((datas==null) || (datas.size()==0)) {
            return lanes;

        }
        StorageLaneSummaryReportDto lane=null;
        BigDecimal total=BigDecimal.ZERO;
        int workcount=0;
        int fullcount=0;
        int halfcount=0;
        int epalfcount=0;
        int reservecount=0;
        for (StorageLaneSummaryAmountReportDto d:datas){
            if (d==null){
                continue;
            }
            lane = new StorageLaneSummaryReportDto();
            lane.setDivisionid(d.getDivisionid());
            lane.setLanenumber(d.getLanenumber());
            total=new BigDecimal(d.getTotal());
            //
            //fullcount=d.getFullcontainercount();
            fullcount=d.getWorkcount();
            lane.setFullcontainerpercent(new BigDecimal(fullcount).divide(total, 4, RoundingMode.HALF_UP));
            //
            halfcount=d.getHalfcontainercount();
            lane.setHalfcontainerpercent(new BigDecimal(halfcount).divide(total, 4, RoundingMode.HALF_UP));
            //
            epalfcount=d.getEmptycontainercount();
            lane.setEmptycontainerpercent(new BigDecimal(epalfcount).divide(total,4, RoundingMode.HALF_UP));
            //
            reservecount=d.getReservecount();
            lane.setReservepercent(new BigDecimal(reservecount).divide(total,4, RoundingMode.HALF_UP));
            //fix
            lane.setReservepercent(BigDecimal.ONE.subtract(lane.getFullcontainerpercent()).subtract(lane.getHalfcontainerpercent()).subtract(lane.getEmptycontainerpercent()));

            lanes.add(lane);
        }
        //
        Collections.sort(lanes, new Comparator<StorageLaneSummaryReportDto>() {
            @Override
            public int compare(StorageLaneSummaryReportDto d1, StorageLaneSummaryReportDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                return d1.getLanenumber().compareTo(d2.getLanenumber());
            }
        });
        return lanes;
    }

    @SuppressWarnings("unchecked")
    public StorageAgeSummaryReportDto summaryStorageAgeData(Map<String, Object> query) {
        StorageAgeSummaryReportDto reportData = null;

        this.preParameters(query);
        //
        String nativeSql = XcmgAmReportWorkSupport.nativeStorageAgeSummaryData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<StorageAgeSummaryReportDto> datas = XcmgAmReportWorkSupport.translateToStorageAgeSummaryData(result);
        if ((datas==null) || (datas.size()==0)){
            return reportData;
        }
        reportData=datas.get(0);
        return reportData;
    }

    public List<StorageAgeContainerSummaryReportDto> summaryStorageAgeContainerData(Map<String, Object> query) {
        List<StorageAgeContainerSummaryReportDto> lines=new ArrayList<>();

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeStorageAgeContainerSummaryData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        // 记录实体转换
        lines = XcmgAmReportWorkSupport.translateToStorageAgeContainerSummaryData(result);
        if ((lines==null) || (lines.size()==0)) {
            return lines;

        }
        StorageAgeContainerSummaryReportDto line=null;
        BigDecimal total=BigDecimal.ZERO;
        for (StorageAgeContainerSummaryReportDto d:lines){
            if (d==null){
                continue;
            }
            if (d.getType().equalsIgnoreCase("A01")){
                d.setTypename("0-30 天");
            }
            if (d.getType().equalsIgnoreCase("A02")){
                d.setTypename("31-90 天");
            }
            if (d.getType().equalsIgnoreCase("A03")){
                d.setTypename("90-180 天");
            }
            if (d.getType().equalsIgnoreCase("A04")){
                d.setTypename("180-360 天");
            }
            if (d.getType().equalsIgnoreCase("A05")){
                d.setTypename("360-720 天");
            }
            if (d.getType().equalsIgnoreCase("A06")){
                d.setTypename("720+天");
            }
        }
        //
        Collections.sort(lines, new Comparator<StorageAgeContainerSummaryReportDto>() {
            @Override
            public int compare(StorageAgeContainerSummaryReportDto d1, StorageAgeContainerSummaryReportDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                return d1.getType().compareTo(d2.getType());
            }
        });
        return lines;
    }

    public Page<StorageAgeListReportDto> queryStorageAgeListData(Map<String, Object> query, Pageable pageable, String databaseType) {
        List<StorageAgeListReportDto> content=new ArrayList<>();

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeStorageAgeListData(query);
        Query q = em.createNativeQuery(nativeSql);


        // 分页设置
        q.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        q.setMaxResults(pageable.getPageSize());

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        // 记录实体转换
        content = XcmgAmReportWorkSupport.translateToStorageAgeListData(result);
        if (content==null){
            content=new ArrayList<>();
        }
        return new PageImpl(content, pageable,
                this.nativeCount(query, XcmgAmReportWorkSupport.nativeStorageAgeListSelect(query), new String[0]).longValue());
    }


    public TransDailySummaryReportDto summaryTransTaskDailyReportDto(Map<String, Object> query){
        TransDailySummaryReportDto reportData = null;

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeTransTaskDialySummaryData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        List<TransDailySummaryReportDto> dlines = XcmgAmReportWorkSupport.translateToTransDailySummaryData(result);
        if ((dlines==null) || (dlines.size()==0)){
            reportData = new TransDailySummaryReportDto();
            return reportData;
        }
        Long divisionid=null;
        Object divisioncode=null;
        int worktotal=0;
        for (TransDailySummaryReportDto d:dlines){
            if (d==null){
                continue;
            }
            worktotal=d.getReceivetotal()+d.getShiptotal()+d.getTasktotal();
            d.setWorktotal(worktotal);

            reportData=d;
            divisionid=null;
            try{
                if ((query!=null) && (query.containsKey("divisioncode"))){
                    divisioncode=query.get(divisioncode);
                    if (divisioncode!=null){
                        divisionid=this.divisionService.retrieveOn(divisioncode.toString(),XcmgAmConstants.DIVISION_WH101_ID);
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            if (divisionid!=null){
                if (divisionid.equals(d.getDivisionid())){
                    break;
                }
            }
        }
        return reportData;
    }

    public List<TransMonthSummaryReportDto> summaryTransMonthData(Map<String, Object> query) {
        List<TransMonthSummaryReportDto> lines=new ArrayList<>();

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeTransMonthSummaryData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        // 记录实体转换
        lines = XcmgAmReportWorkSupport.translateToTransMonthData(result);
        if ((lines==null) || (lines.size()==0)) {
            return lines;
        }

        int size=lines.size();
        Long divid=lines.get(0).getDivisionid();
        TransMonthSummaryReportDto line=null;
        if (size<12){
            for (int k=0; k<=12; k++ ){
                line = new TransMonthSummaryReportDto();
                line.setDivisionid(divid);
                line.setMonthcode("");
                line.setReceivecount(null);
                line.setShipcount(null);
                line.setWorkcount(null);
                lines.add(line);
            }
        }
        for (TransMonthSummaryReportDto d:lines){
            if (d==null){
                continue;
            }
            if (StringUtils.isNullOrBlank(d.getMonthcode())){
                continue;
            }
            if (d.getMonthcode().length()==6){
                d.setMonthcode(d.getMonthcode().substring(4)+"月");
            }

        }
        /**
        Collections.sort(lines, new Comparator<TransMonthSummaryReportDto>() {
            @Override
            public int compare(TransMonthSummaryReportDto d1, TransMonthSummaryReportDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                return d1.getMonthcode().compareTo(d2.getMonthcode());
            }
        });
         **/
        return lines;
    }

    public List<StationSummaryDailyReportDto> summaryStationWorkDailyData(Map<String, Object> query,
                                       Map<String, String> stationStatus) {

        List<StationSummaryDailyReportDto> lines=new ArrayList<>();
        Map<String, StationSummaryDailyReportDto> linesMap=new HashMap<>();
        StationSummaryDailyReportDto line=null;
        this.preParameters(query);
        Long divisionid=XcmgAmConstants.DIVISION_WH101_ID;
        if (query.get(XcmgAmConstants.DIVISION_ID_FIELD_CODE)!=null){
            divisionid = (Long)query.get(XcmgAmConstants.DIVISION_ID_FIELD_CODE);
        }
        int total=0;
        BigDecimal lastpercent=BigDecimal.ONE;
        /**
        //
        if (divisionid.equals(AvicWuhanConstants.DIVISION_WH209_ID)){
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SA0101));
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SA0102));
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SA0103));
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SA0501));
        }

        if (divisionid.equals(AvicWuhanConstants.DIVISION_WH901_ID)){
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SB0101));
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SB0102));
            lines.add(new com.pi.pro.avic.wuhan.common.vo.report.StationSummaryDailyReportDto(AvicWuhanConstants.LOCATION_CODE_SB0103));
        }
        for (StationSummaryDailyReportDto s:lines){
            if (s==null){
                continue;
            }
            linesMap.put(s.getStationcode(), s);
        }

        String flag=null;
        if (stationStatus!=null){
            for (StationSummaryDailyReportDto d:lines){
                if (d==null){
                    continue;
                }
                flag=stationStatus.get(d.getStationcode());
                if (CodesConstants.COMMON_FLAG_YES.equalsIgnoreCase(flag)){
                    d.setWorkstatus(AvicWuhanConstants.STATION_WORK_STATUS_WORK);
                }
            }
        }
        **/
        String nativeSql=XcmgAmReportWorkSupport.nativeStationWorkSummaryDailyData(query);
        Query q=em.createNativeQuery(nativeSql);
        List<Object[]> result=q.getResultList();
        List<StationSummaryItemDto> datas=XcmgAmReportWorkSupport.translateToStationWorkSummryhData(result);
        if ((datas!=null) && (datas.size()>0)) {
            for (StationSummaryItemDto d:datas) {
                if (d==null) {
                    continue;
                }
                line=linesMap.get(d.getStationcode());
                if (line==null) {
                    continue;
                }
                line.setFincount(d.getTotal());
                total=total+d.getTotal();
            }
        }

        //待作业-收货
        nativeSql=XcmgAmReportWorkSupport.nativeStationHoldPutawaySummaryDailyData(query);
        q=em.createNativeQuery(nativeSql);
        result=q.getResultList();
        datas=XcmgAmReportWorkSupport.translateToStationWorkSummryhData(result);
        if ((datas!=null) && (datas.size()>0)) {
            for (StationSummaryItemDto d:datas) {
                if (d==null) {
                    continue;
                }
                line=linesMap.get(d.getStationcode());
                if (line==null) {
                    continue;
                }
                line.setWorkingcount(d.getTotal());
                total=total + d.getTotal();
            }
        }

        //待作业-发货
        nativeSql=XcmgAmReportWorkSupport.nativeStationHoldPickSummaryDailyData(query);
        q=em.createNativeQuery(nativeSql);
        result=q.getResultList();
        datas=XcmgAmReportWorkSupport.translateToStationWorkSummryhData(result);
        int count=0;
        if ((datas!=null) && (datas.size()>0)){
            for (StationSummaryItemDto d:datas){
                if (d==null){
                    continue;
                }
                line=linesMap.get(d.getStationcode());
                if (line==null){
                    continue;
                }
                count=line.getWorkingcount();
                if ((d.getTotal()!=null) && (d.getTotal()>0)){
                    count=count+d.getTotal();
                }
                line.setWorkingcount(count);
                total=total+d.getTotal();
            }
        }

        //
        // 记录实体转换
        count = 0;
        if (total>0) {
            for (int i=0; i <lines.size()-1; i++) {
                line=lines.get(i);
                if (line!= null) {
                    count=line.getFincount()+line.getWorkingcount();
                    line.setWorkpercent((new BigDecimal(count /(total*1.0))).setScale(4, RoundingMode.HALF_UP));
                    lastpercent=lastpercent.subtract(line.getWorkpercent());
                }
            }
            line=lines.get(lines.size()-1);
            line.setWorkpercent(lastpercent.setScale(4, RoundingMode.HALF_UP));
        }
        //
        Collections.sort(lines, new Comparator<StationSummaryDailyReportDto>() {
            @Override
            public int compare(StationSummaryDailyReportDto d1, StationSummaryDailyReportDto d2) {
                if (d1==null){
                    return -1;
                }
                if (d2==null){
                    return 1;
                }
                return d1.getStationcode().compareTo(d2.getStationcode());
            }
        });
        return lines;
    }

    public TaskSummaryDailyReportDto summaryTaskDailyReportDto(Long divisionId, Date date){
        TaskSummaryDailyReportDto reportData=new TaskSummaryDailyReportDto();
        List<TwtActTaskList> tasks=this.dyncdataQueryService.queryDailyTask(divisionId, date);
        if ((tasks==null) || (tasks.size()==0)){
            return reportData;
        }
        Set<String> paconts=new HashSet<>();
        Set<String> pkconts=new HashSet<>();
        Set<String> allconts=new HashSet<>();
        for (TwtActTaskList t:tasks){
            if (t==null){
                continue;
            }
            if (StringUtils.isNullOrBlank(t.getType())){
                continue;
            }
            if (XcmgAmConstants.MATERIAL_ID_EPAL.equals(t.getMaterialId())){
                continue;
            }
            if (WmsConstants.TASK_TYPE_MOVE.equalsIgnoreCase(t.getType())){
                continue;
            }
            allconts.add(t.getFromContainNumber());
            if (WmsConstants.TASK_STATUS_CLOSE.equalsIgnoreCase(t.getStatus())){
                if (WmsConstants.TASK_TYPE_PUTAWAY.equalsIgnoreCase(t.getType())){
                    paconts.add(t.getFromContainNumber());
                }
                if (WmsConstants.TASK_TYPE_PICK.equalsIgnoreCase(t.getType())){
                    paconts.add(t.getFromContainNumber());
                }
                continue;
            }
        }
        reportData.setTotal(allconts.size());
        reportData.setPapalletcount(paconts.size());
        reportData.setPkpalletcount(pkconts.size());
        return reportData;
    }

    public List<TransConsDailySummaryReportDto> summaryTransConsTaskDailyReportData(Map<String, Object> query) {
        List<TransConsDailySummaryReportDto> lines=new ArrayList<>();

        this.preParameters(query);

        /**
         * 已完成单证数/作业笔数
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeTransConsDailySummaryData(query);
        Query q = em.createNativeQuery(nativeSql);

        List<Object[]> result = q.getResultList();

        // 记录实体转换
        // 记录实体转换
        lines = XcmgAmReportWorkSupport.translateToTransConsDailySummaryData(result);
        if ((lines==null) || (lines.size()==0)) {
            return lines;
        }

        Collections.sort(lines , new Comparator<TransConsDailySummaryReportDto>(){
            public int compare(TransConsDailySummaryReportDto a , TransConsDailySummaryReportDto b){
                return a.getReportdate().compareToIgnoreCase(b.getReportdate());
            }
        });

        return lines;
    }


//------------------------------------------------------------------------------------------------------------------
//  明细数据
//------------------------------------------------------------------------------------------------------------------

    public List<CommonDocumentDetailsDto> queryInboundTodayDocumentData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> reportData = null;

        this.preParameters(query);

        /**
         * 当天入库单证
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeInboundTodayDocumentData(query, pagination);
        Query q = em.createNativeQuery(nativeSql);
        List<Object[]> result = q.getResultList();

        // 记录实体转换
        reportData = XcmgAmReportWorkSupport.translateToInboundTodayDocData(result);
        if (reportData!=null) {
            for (CommonDocumentDetailsDto line:reportData){
                XcmgAmSupport.asnStatusOf(line);
            }
        }
        return reportData;
    }

    public List<CommonTransDetailsDto> queryInboundReceiveTransData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonTransDetailsDto> reportData = null;

        this.preParameters(query);

        /**
         * 当天已收货交易
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeInboundTodayReceiveTransSumData(query, pagination);
        Query q = em.createNativeQuery(nativeSql);
        List<Object[]> result = q.getResultList();

        // 记录实体转换
        reportData = XcmgAmReportWorkSupport.translateToInboundTodayReceiveTransSumData(result);

        return reportData;
    }

    public List<CommonInventoryDetailsDto> queryInboundHoldInventoryData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonInventoryDetailsDto> reportData = null;
        this.preParameters(query);

        /**
         * 当天已收货待上架
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeInboundTodayHoldStorageData(query, pagination);
        Query q = em.createNativeQuery(nativeSql);
        List<Object[]> result = q.getResultList();

        // 记录实体转换
        reportData = XcmgAmReportWorkSupport.translateToInboundTodayHoldStorageData(result);
        if (reportData!=null) {
            for (CommonInventoryDetailsDto line:reportData){
                XcmgAmSupport.inventoryStatusOf(line);
            }
        }
        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundTodayDocumentData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> reportData = null;

        this.preParameters(query);

        /**
         * 当天出库单证
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeOutboundTodayDocumentData(query, pagination);
        Query q = em.createNativeQuery(nativeSql);
        List<Object[]> result = q.getResultList();

        // 记录实体转换
        reportData = XcmgAmReportWorkSupport.translateToOutboundTodayDocData(result);
        if (reportData!=null) {
            for (CommonDocumentDetailsDto line:reportData){
                XcmgAmSupport.soStatusOf(line);
                line.setQtyshortage(line.getQtyordered());
                line.setQtyshortageeach(line.getQtyorderedeach());
                if ((line.getQtyallocated()!=null)
                        && (line.getQtyallocated().compareTo(BigDecimal.ZERO))>0){
                    line.setQtyshortage(line.getQtyordered().subtract(line.getQtyallocated()));
                    line.setQtyshortageeach(line.getQtyorderedeach().subtract(line.getQtyallocatedeach()));
                }
            }
        }
        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundFinishedDocumentData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> allData = this.queryOutboundTodayDocumentData(query, pagination);
        List<CommonDocumentDetailsDto> reportData = new ArrayList<>();
        if (allData!=null) {
            for (CommonDocumentDetailsDto line:allData){
                if (line==null){
                    continue;
                }
                if ((WmsConstants.SO_STATUS_SHIP.equalsIgnoreCase(line.getDocumentstatus()))
                        || (WmsConstants.SO_STATUS_CLOSE.equalsIgnoreCase(line.getDocumentstatus()))){
                    reportData.add(line);
                }
            }
        }
        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundShortagedDocumentData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> allData = this.queryOutboundTodayDocumentData(query, pagination);
        List<CommonDocumentDetailsDto> reportData = new ArrayList<>();
        if (allData!=null) {
            for (CommonDocumentDetailsDto line:allData){
                if (line==null){
                    continue;
                }
                if ((WmsConstants.SO_STATUS_SHIP.equalsIgnoreCase(line.getDocumentstatus()))
                        || (WmsConstants.SO_STATUS_CLOSE.equalsIgnoreCase(line.getDocumentstatus()))
                        || (WmsConstants.SO_STATUS_CANCELL.equalsIgnoreCase(line.getDocumentstatus())) ){
                   continue;
                }
                if ((line.getQtyallocated()==null)
                        || ((line.getQtyallocated().compareTo(BigDecimal.ZERO))<=0)
                        || ((line.getQtyallocated().compareTo(line.getQtyordered()))<0)) {
                    reportData.add(line);
                }
            }
        }
        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundDocumentSumData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> reportData = null;

        this.preParameters(query);

        /**
         * 当天出库单证-按物料+批次汇总
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeOutboundTodayDocumentSumData(query, pagination);
        Query q = em.createNativeQuery(nativeSql);
        List<Object[]> result = q.getResultList();

        // 记录实体转换
        reportData = XcmgAmReportWorkSupport.translateToOutboundTodayDocSumData(result);
        if (reportData!=null) {
            for (CommonDocumentDetailsDto line:reportData){
                XcmgAmSupport.soStatusOf(line);
            }
        }
        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundDocumentShipSumData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> allData = this.queryOutboundDocumentSumData(query, pagination);
        List<CommonDocumentDetailsDto> reportData =new ArrayList<>();

        if (allData!=null) {
            for (CommonDocumentDetailsDto line:allData){
                if (line==null){
                    continue;
                }
                line.setQtyshortage(line.getQtyordered());
                line.setQtyshortageeach(line.getQtyorderedeach());
                if ((line.getQtyallocated()!=null)
                        && ((line.getQtyallocated().compareTo(BigDecimal.ZERO))>0)) {
                    //
                    line.setQtyshortage(line.getQtyordered().subtract(line.getQtyallocated()));
                    line.setQtyshortageeach(line.getQtyorderedeach().subtract(line.getQtyallocatedeach()));

                    reportData.add(line);
                }
            }
        }
        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundDocumentDetailsData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> reportData = null;

        this.preParameters(query);

        /**
         * 出库单证
         */
        String nativeSql = XcmgAmReportWorkSupport.nativeOutboundDocumentDetailsData(query, pagination);
        Query q = em.createNativeQuery(nativeSql);
        List<Object[]> result = q.getResultList();

        // 记录实体转换
        reportData = XcmgAmReportWorkSupport.translateToOutboundTodayDocData(result);
        if (reportData!=null) {
            for (CommonDocumentDetailsDto line:reportData){
                XcmgAmSupport.soStatusOf(line);
            }
        }

        return reportData;
    }

    public List<CommonDocumentDetailsDto> queryOutboundShortageDocumentDetailsData(Map<String, Object> query, ReportPageQueryDto pagination) {
        List<CommonDocumentDetailsDto> allData = this.queryOutboundDocumentDetailsData(query, pagination);
        List<CommonDocumentDetailsDto> reportData = new ArrayList<>();

        if (allData!=null) {
            for (CommonDocumentDetailsDto line:allData){

                if ((line.getQtyallocated()==null)
                        || (line.getQtyallocated().compareTo(BigDecimal.ZERO)<=0)
                        || (line.getQtyallocated().compareTo(line.getQtyordered())<0)){
                    line.setQtyshortage(line.getQtyordered().subtract(line.getQtyallocated()));
                    line.setQtyshortageeach(line.getQtyorderedeach().subtract(line.getQtyallocatedeach()));
                    XcmgAmSupport.soStatusOf(line);
                    reportData.add(line);
                }
            }
        }
        return reportData;
    }

//------------------------------------------------------------------------------------------------------------------
//  func
//------------------------------------------------------------------------------------------------------------------


    private void preParameters(Map<String, Object> query){
        if ((query==null) || (query.size()==0)){
            return;
        }
        Long divisionid=null;
        if (query.get("divisioncode") != null) {
            divisionid=this.divisionService.retrieve(query.get("divisioncode").toString().trim());
            if (divisionid!=null){
                query.put(XcmgAmConstants.DIVISION_ID_FIELD_CODE, divisionid);
            }
        }
    }

    private Long nativeCount(Map<String, Object> query, String nativeSql, String... filterParamNames) {
        Query q = em.createNativeQuery(WmsReportUtils.nativeCount(nativeSql));
        //setParameter(query, q, filterParamNames);
        Object obj = q.getSingleResult();
        return obj == null ? 0 : Long.parseLong(obj.toString());
    }



}
